package br.com.cast.syscast.persistencia;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.springframework.stereotype.Component;

import br.com.cast.syscast.models.Pais;
import br.com.cast.syscast.models.Uf;

@Component 
public class UfDAO extends DAOBase {
	
	public UfDAO() {
	}
	
	public Iterator<Uf> consultaPaginada(long inicio, long fim) {
		
		List<Uf> ufs = new ArrayList<>();
		
		String sql = "SELECT * FROM ("
						+ " SELECT u.*, p.id idPais, p.nome nomePais, "
						+ "  ROW_NUMBER() OVER (ORDER BY u.id) as row"
						+ " FROM uf u "
						+ "		INNER JOIN pais p "
						+ "			ON p.id = u.id_pais "
					+ ") a WHERE a.row > ? and a.row <= ?";
		
		try {
			PreparedStatement ps = getConnection().prepareStatement(sql);
			
			ps.setLong(1, inicio);
			ps.setLong(2, fim);
			
			ResultSet rs = ps.executeQuery();
			
			while (rs.next()) {
				
				Uf uf = new Uf();
				uf.setId(rs.getInt("id"));
				uf.setNome(rs.getString("nome"));
				uf.setSigla(rs.getString("sigla"));
				
				uf.setPais(new Pais(
						rs.getInt("idPais"), 
						rs.getString("nomePais")));
				
				ufs.add(uf);
			}
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		return ufs.iterator();
	}
	
	public void salvar(Uf uf) {
		
		String sql = "insert into uf (sigla, nome, id_pais)"
				+ " values(?, ?, ?);";
		
		try {
			PreparedStatement ps = getConnection().prepareStatement(sql);
			
			ps.setString(1, uf.getSigla());
			ps.setString(2, uf.getNome());
			ps.setInt(3, uf.getPais().getId());
			
			ps.execute();
			
			ps.close();
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public void alterar(Uf uf) {
		
		String sql = "update uf set sigla = ? "
					+ ", nome = ? "
					+ ", id_pais = ? "
					+ "where id = ?";
		
		try {
			PreparedStatement ps = getConnection().prepareStatement(sql);
			
			ps.setString(1, uf.getSigla());
			ps.setString(2, uf.getNome());
			ps.setInt(3, uf.getPais().getId());
			ps.setInt(4, uf.getId());
			
			ps.execute();
			
			ps.close();
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public long total() {
		String sql = "SELECT COUNT(*) as total "
				+ "FROM uf";

		Long total = null;
		
		try {
			PreparedStatement ps = getConnection().prepareStatement(sql);
			
			ResultSet rs = ps.executeQuery();
			
			if (rs.next()) {
				total = rs.getLong("total");			
			}
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		return total;
	}
	
	public void excluir(int id) {
		String sql = "DELETE FROM uf where id = ?";

		try {
			PreparedStatement ps = getConnection().prepareStatement(sql);
			ps.setInt(1, id);
			
			ps.execute();
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public boolean ufJaCadastrada(Uf uf) {
		
		String sql = "SELECT COUNT(*) as qtd "
					+ "FROM uf "
					+ "WHERE sigla = ? "
					+ "AND id_pais = ?";
		
		if (uf.getId() != null) {//alteração
			sql += " AND id <> ? ";
		}

		Long qtd = null;
		
		try {
			PreparedStatement ps = getConnection().prepareStatement(sql);
			
			ps.setString(1, uf.getSigla());
			ps.setInt(2, uf.getPais().getId());
			
			if (uf.getId() != null) {//alteração
				ps.setInt(3, uf.getId());
			}
			
			ResultSet rs = ps.executeQuery();
			
			if (rs.next()) {
				qtd = rs.getLong("qtd");			
			}
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		return qtd > 0;
	}

	public List<Uf> consultaTodos() {
		List<Uf> ufs = new ArrayList<>();
		
		StringBuilder sql = new StringBuilder();
		
		sql.append(" SELECT u.*, p.id idPais, p.nome nomePais ")
			.append(" FROM uf u ")
			.append(" 	INNER JOIN pais p")
			.append("	  ON p.id = u.id_pais");
		
		try {
			PreparedStatement ps = getConnection()
					.prepareStatement(sql.toString());
			
			ResultSet rs = ps.executeQuery();
			
			while (rs.next()) {
				
				Uf uf = new Uf();
				
				uf.setId(rs.getInt("id"));
				uf.setNome(rs.getString("nome"));
				uf.setSigla(rs.getString("sigla"));
				uf.setPais(new Pais(
						rs.getInt("idPais"), 
						rs.getString("nomePais")));
				
				ufs.add(uf);
			}
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		return ufs;
	}
	
}
